Hi everyone in this video.
We are going to cover basic mathematical operations.
And by the end of this video you will be able to appreciate the fact that Excel makes it very easy to
do repetitive mathematical operations.
Let's start with this lecture.
I'll be working on this workbook.
We have a table here of two columns containing numbers.
Here are the tasks that I have to perform.
My first task is to add the two numbers.
And right there some in the column by some then in the fourth column I will add these two numbers and
I will add a constant as well.
This is the costing that will be adding along with these two numbers
in the Fifth Column.
We will multiply these three numbers.
And in the last column we will find the average Joe the three numbers.
All in all what these 10 rows of numbers.
I have 40 cells to be filled.
Looks like a big task even if we use a calculator.
It may take us 15 minutes to do this maybe even more.
Don't worry index it.
It won't take that long.
Let's see how first we want to add two numbers for this.
We need to write a formula because a calculation is to be done.
A formula starts with an equal to symbol.
This symbol tells excel that the calculation is to be done in this way.
So rewrite is equal to click on the first will put a plus symbol and then click on these seconds and
you can see that the two says part of this formula are highlighted in colored boxes.
Now when I hit enter Excel will calculate the value from the formula.
And short here if you click the cell you can see that formula bar is still showing the actual formula
but the cell is showing the calculated value of the formula.
If you want to edit the formula you can either do it in the formula bar or double click on the cell
and then edit the formula.
Now you may have this question in mind why in the formula did we add to says why is it a two plus B
two and not five plus six.
Well if we write five plus six even then we will get the same answer.
But using cell reference Nat is writing a two plus B2 gives us a little benefit the first benefit is
that this makes the cell dynamic good changes.
For example if you update this number to 50
then you'd need not correct your formula again your formula will add the updated contents of these two
cells and until you these new ones that had you written five plus six.
Initially you would need to rectify this formula every time you make any changes to these two numbers.
The second benefit is simply amazing Exxon offers a functionality of extending cities and formulas from
one cell to the other.
If you go to the bottom right corner of this you will see that because of changes to a small plus symbol
click and hold and not drag down you'll see this black border extending to the next cell you again extended
as far as you would like to.
But first let's see what happens with this one set in this cell.
You can see that the sum is calculated in the formula bar.
You see that automatically it is adding A3 and beat no we entered the formula in C2 for adding A2 and
we do.
But when we extended the formula it was automatically updated to A3 and B3 the logic followed is that
when you extend the formula to the next row all the cell references are updated or the next group.
Same words for columns.
If I extend the formula to the right that is to the next column of cell references will point to the
next column cells in the new formula.
Let's see this.
Indeed they will in the right
here.
The formula in entered is 80 plus a Ford.
If I extend this formula to the right
the new formula states JT plus G4.
That is when I extend the formula to a distant cell which has a new column number all the cell references
in the formula are changed accordingly.
So can you see the power now to fill this entire column.
I need not add 10 times.
Just write the formula once and drag it to all the other set and it's done.
You can see this column is ready
for the next column.
We have to add key numbers.
Number one.
Number two and the constant let's state the formula for that is equal to A2 plus B2 plus this constant.
I would like to extend this formula to other cells in this column but when I extended
the road reference is updated for all these cells A2 becomes A3 B2 because B3 and B 13 become B 14
to becoming three is okay for me here but I do not want this thirteen to increase to fourteen because
my constant stays in cell betadine only
to stop this from happening.
That is to stop the different thing to be updated.
We can use dollar symbols.
Let me show you how in this formula a cell is referenced using column name and ruining.
If you drag the formula down through a name is updated but your name is the number so two becomes three.
If you put a dollar symbol dollar symbol as a symbol.
Now do you find over the keyboard.
Number four.
So if you press shift and then press 4 you will get a dollar symbol.
If you add a dollar symbol in front of the row name and then drag down the formula
you will notice that that cell reference which has a dollar symbol in front of it will not be updated
and will be logged.
So if you compare the formula in draw to Andrew three A2 becomes a three B2 becomes B3 but betadine
stays as V 13.
This is because 13 was logged with a dollar symbol
let's extend it to all the cells in this column.
You can check it is working fine for all these things we can do the same thing for columns also for
columns when we drag the formula from left to right column names are changed.
Putting a dollar symbol in front of column name will lock it.
So now if I extend this horizontally you'll see that a tree becomes I will becomes due for a six becomes
G six I want ISIS to remain as Isaac's and not become D6 so I will put our dollar symbol
here in front of ISIS.
This means I will be logged.
So when I extend this formula horizontally here you'll see G3 plus G4 plus A6
What if we put a dollar symbol on board.
Probably you guessed it right.
If you put a dollar symbol on board the wrong name and a column name would the law change regardless
of where you moved the formula.
Now in the next two columns we know what to do next write the formula first or product.
We will select the first number the multiply symbol is the aesthetic which can be found on number eight
in the keyboard with number two
multiplied with the number three with dollar symbol in front of the door number
and in the average column
will need to add the three numbers first and then divide them by three to the average so since we have
to add the three numbers first we will write some action in the brackets so within record number one
plus number two.
Plus this constant with our dollar symbol
you close this packet and divide this total sum that three
now in this fourth column.
We can also use the sum calculated in the column B in the column B.
We already have the sum we can straightaway use this value and divided by three in instead of writing
this formula we can also write is equal to B2 divided by three so the formulas are written in these
two cities.
Now here's an added handy trick.
You can select Moody says go to the bottom right corner and double click
when you're definitely Excel automatically filled it up for the entire table so you don't have to click
and hold the bottom right corner and drag it to the entire table.
You can just go to the bottom right corner and double click and the entire table will repeat
I hope you can now see how Excel increases productivity in tasks which involve repetitive calculations
in the coming videos.
We will see other functions mathematical textual logical functions that are commonly used to perform
complex tasks in exit.